const Sequelize = require('sequelize');
const response = require('../../utils/response.js');
const validate = require('../../utils/validate');
const { getStatisticsRules } = require('../validations/statisticsValidation.js');
const sequelize = require('../db/index.js');

async function fetchData(model, year) {
    try {
        // 验证 year 是否为有效数字，如果不是则默认为当前年份
        const inputYear = Number(year);
        const isValidYear = !isNaN(inputYear) && inputYear > 0;

        const today = new Date();
        today.setHours(0, 0, 0, 0);

        // 计算开始日期
        const startDate = isValidYear ? new Date(inputYear, 0, 1) : today;

        const [{ total }] = await sequelize.query(`
          SELECT COUNT(created_at) as total
          FROM
          ${model}
        `, {
            type: Sequelize.QueryTypes.SELECT
        });
        const weeklyCounts = await sequelize.query(`
          SELECT
            COUNT(created_at) as count,
            DAYOFWEEK(created_at) - 1 as dayOfWeek
          FROM
            ${model}
          WHERE
            created_at >= :startDate
          GROUP BY
            dayOfWeek
        `, {
            type: Sequelize.QueryTypes.SELECT,
            replacements: { startDate },
        });

        const monthlyCounts = await sequelize.query(`
          SELECT
            COUNT(created_at) as count,
            MONTH(created_at) - 1 as month
          FROM
            ${model}
          WHERE
            created_at >= :startDate
          GROUP BY
            month
        `, {
            type: Sequelize.QueryTypes.SELECT,
            replacements: { startDate },
        });

        const result = {
            total: total,
            weeks: Array.from({ length: 7 }, (_, i) => weeklyCounts.find(item => item.dayOfWeek === i)?.count || 0),
            months: Array.from({ length: 12 }, (_, i) => monthlyCounts.find(item => item.month === i)?.count || 0),
        };

        return result;
    } catch (error) {
        console.error('Error:', error);
    }
}

async function fetchAmountData(model, year) {
    try {
        // 验证 year 是否为有效数字，如果不是则默认为当前年份
        const inputYear = Number(year);
        const isValidYear = !isNaN(inputYear) && inputYear > 0;

        const today = new Date();
        today.setHours(0, 0, 0, 0);

        // 计算开始日期
        const startDate = isValidYear ? new Date(inputYear, 0, 1) : today;

        const [{ total }] = await sequelize.query(`
            SELECT
                SUM(total) as total
            FROM
                ${model}
            WHERE
               paymentStatus = 'paid'
        `, {
            type: Sequelize.QueryTypes.SELECT
        });
        const weeklyCounts = await sequelize.query(`
            SELECT
                SUM(total) as count,
                DAYOFWEEK(created_at) - 1 as dayOfWeek
            FROM
                ${model}
            WHERE
                created_at >= :startDate AND paymentStatus = 'paid'
            GROUP BY
                dayOfWeek
            `, {
            type: Sequelize.QueryTypes.SELECT,
            replacements: { startDate },
        });

        const monthlyCounts = await sequelize.query(`
          SELECT
            SUM(total) as count,
            MONTH(created_at) - 1 as month
          FROM
            ${model}
          WHERE
            created_at >= :startDate AND paymentStatus = 'paid'
          GROUP BY
            month
        `, {
            type: Sequelize.QueryTypes.SELECT,
            replacements: { startDate },
        });

        const result = {
            total: Number(total),
            weeks: Array.from({ length: 7 }, (_, i) => weeklyCounts.find(item => item.dayOfWeek === i)?.count - 0 || 0),
            months: Array.from({ length: 12 }, (_, i) => monthlyCounts.find(item => item.month === i)?.count - 0 || 0),
        };

        return result;
    } catch (error) {
        console.error('Error:', error);
    }
}

class statisicsController {
    async getStatisics(ctx) {
        try {
            const { year } = await validate(ctx, getStatisticsRules);
            let member = await fetchData('Users', year);
            response.success(ctx, {
                member,
                order: {},
                reserve: {},
                amount: {}
            });
        } catch (err) {
            console.log(err, 'getMemberStatisics');
            response.error(ctx, err);
        }
    }
}

module.exports = new statisicsController();